package com.bdqn.tour.dao.impl;

import com.bdqn.tour.dao.BaseDao;
import com.bdqn.tour.dao.HqfSearchMessageDao;
import com.bdqn.tour.pojo.Scenic;


import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class Hqfsearchmessagedaoimpl extends BaseDao implements HqfSearchMessageDao {

    @Override
    public List<Scenic> getScenicList() {
        //创建Scenic对象列表，存储信息
        List<Scenic> list = new ArrayList<>();
        //获取数据库连接Connection
        Connection conn = this.getConnection();
        //初始化对象PreparedStatement，ResultSet
        PreparedStatement psmt = null;
        ResultSet rs = null;
        try {
            //编写sql语句查询所有数据
            String sql = "SELECT * FROM scenictable";
            //创建预编译对象
            psmt = conn.prepareStatement(sql);
            //执行查询获取结果集
            rs = psmt.executeQuery();
            //遍历结果集
            while (rs.next()) {
                int id = rs.getInt("scenic_Id");
                String name = rs.getString("scenic_name");
                String address = rs.getString("scenic_address");
                int price = rs.getInt("price");
                String img = rs.getString("img");
                int satisfaction = rs.getInt("satisfaction");
                String city = rs.getString("city");
                String html = rs.getString("html");
                list.add(new Scenic(id,name,address,price,img,satisfaction,city,html));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            closeAll(conn,psmt,rs);
        }
        return list;
   }


   //计算总分页数
   public Integer getEmpsPageCount() {
       int count = 0;
       Connection conn = this.getConnection();
       PreparedStatement psmt = null;
       ResultSet rs = null;
       try {
           String sql = "select count(*) from scenictable";
           psmt = conn.prepareStatement(sql);
           rs = psmt.executeQuery();
           while (rs.next()) {
               //对象的列索引是从 1 开始的
             count = rs.getInt(1);
           }
       } catch (SQLException e) {
           e.printStackTrace();
       } finally {
           closeAll(conn,psmt,rs);
       }
       return count;
   }



   //分页查询内容
    @Override
    public List<Scenic> getEmpsByPage(int startIndex, int pageSize) {
        //1. 返回对象
        List<Scenic>  list = new ArrayList<>();
        //2.打开连接
        this.conn = this.getConnection();
        PreparedStatement psmt = null;
        ResultSet rs = null;

        //3.准备SQL,对User表进行分页处理
        String sql = "select scenic_Id,scenic_name,scenic_address,price,img,satisfaction,city,html from scenictable limit ?,?";
        try {
            psmt = conn.prepareStatement(sql);
            psmt.setInt(1,startIndex);
            psmt.setInt(2,pageSize);
            //4.预编译SQL
            rs = psmt.executeQuery();
            //7.解析结果集
            while(rs.next()){
                Scenic scenic = new Scenic();
                scenic.setScenic_Id(rs.getInt(1));
                scenic.setScenic_name(rs.getString(2));
                scenic.setScenic_address(rs.getString(3));
                scenic.setPrice(rs.getInt(4));
                scenic.setImg(rs.getString(5));
                scenic.setSatisfaction(rs.getInt(6));
                scenic.setCity(rs.getString(7));
                scenic.setHtml(rs.getString(8));
                list.add(scenic);
            }

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            //释放资源
            this.closeAll(conn,psmt,rs);
        }
        return list;

    }
}
